<?php if (!defined('BASEPATH')) exit('No direct script access allowed'); 

// @TODO: Get results only where they are set to active

class Qlsv_model extends CI_Model
{
	protected $_table;	    
	/**
	 * Constructor
	 *
	 * @access	public
	 */
	function __construct()
	{
		parent::__construct();
		$this->load->helper('date');
		date_default_timezone_set('Asia/Ho_Chi_Minh');
	}
	
	/*
	 * Type: Ajax
	 * Added date: 02/11/2014
	 * Added by: ManhVT
	 * Update kind of hocvien_lophoc
	 */
	function fbaoluu($id)
	{
		$kind = 3;
		//1. Update hocvien_lophoc:kind
		$this->db->trans_begin();
		//Get obj before update
		$this->db->select('hocvien_lophoc.*');
		$this->db->from('hocvien_lophoc');
		$this->db->where('hocvien_lophoc.id',$id);
		$query = $this->db->get();
		$hvlophoc = $query->row();
		//var_dump($hvlophoc);
		if($hvlophoc->kind != 0)
		{
			return 0;
		}
		
		$fields = array(
				'kind'		=> $kind,
		);		
		$this->db->set($fields);
		$this->db->where('id', $id);
		$this->db->where('kind', 0);
		if($this->db->update('hocvien_lophoc'))
		{
			//. Nếu thành công cập nhật lại type, current_lopid;lopids of hocvien
			//1. Lấy obj student
			$this->db->select('hocvien.*,hocvien_lophoc.idlop');
			$this->db->from('hocvien');
			$this->db->join('hocvien_lophoc','hocvien.id = hocvien_lophoc.idhv');
			$this->db->where('hocvien_lophoc.id',$id);
			$query = $this->db->get();
			$row = $query->row();
			if($row)
			{
				$lopids = '';
				$lopids = $row->lopids; //String with Json data format,
				$curent_idlop = $row->idlop;
				// Convert json data to array
				$lopid_arr = json_decode($lopids,true);
				
				$current_item['idlop']=$curent_idlop;
				$current_item['kind']=$kind;
				// kiểm tra xem đã tồn tại lopid chưa, có thì cập nhật, chưa thì thêm mới
				$flag = -1;
				if($lopid_arr)
				{
					foreach($lopid_arr as $key=>$lopid_item)
					{
						//var_dump($lopid_item);
						if($lopid_item['idlop']==$curent_idlop)
						{
							$lopid_item['kind'] = $kind;
							$lopid_arr[$key] = $lopid_item;
							$flag = 1;
						}
					}
					if($flag != 1)
					{
						$lopid_arr[] = $current_item;
					}
				}else{
					$lopid_arr[] = $current_item;
				}
				$lopids = json_encode($lopid_arr);				
				
				$fields = array(
					'type'		=> 0,
			   	 	'current_lopid' => 0,
					'lopids'=>	$lopids		    
				);
				$this->db->set($fields);
				$this->db->where('hocvien.id', $row->id);
				$this->db->update('hocvien');
				if ($this->db->trans_status() === FALSE)
				{
				    $this->db->trans_rollback();
				    return 0;
				}
				else
				{
				    $this->db->trans_commit();
				    return 1;
				}
				
			}
		}
		return 0;
	}
	function fdunghoc($id)
	{
		$kind = 2;
		//1. Update hocvien_lophoc:kind
		$this->db->trans_begin();
		//Get obj before update
		$this->db->select('hocvien_lophoc.*');
		$this->db->from('hocvien_lophoc');
		$this->db->where('hocvien_lophoc.id',$id);
		$query = $this->db->get();
		$hvlophoc = $query->row();
		//var_dump($hvlophoc);
		if($hvlophoc->kind != 0)
		{
			return 0;
		}
		
		$fields = array(
				'kind'		=> $kind,
		);		
		$this->db->set($fields);
		$this->db->where('id', $id);
		$this->db->where('kind', 0);
		if($this->db->update('hocvien_lophoc'))
		{
			//. Nếu thành công cập nhật lại type, current_lopid;lopids of hocvien
			//1. Lấy obj student
			$this->db->select('hocvien.*,hocvien_lophoc.idlop');
			$this->db->from('hocvien');
			$this->db->join('hocvien_lophoc','hocvien.id = hocvien_lophoc.idhv');
			$this->db->where('hocvien_lophoc.id',$id);
			$query = $this->db->get();
			$row = $query->row();
			if($row)
			{
				$lopids = '';
				$lopids = $row->lopids; //String with Json data format,
				$curent_idlop = $row->idlop;
				// Convert json data to array
				$lopid_arr = json_decode($lopids,true);
				
				$current_item['idlop']=$curent_idlop;
				$current_item['kind']=$kind;
				// kiểm tra xem đã tồn tại lopid chưa, có thì cập nhật, chưa thì thêm mới
				$flag = -1;
				if($lopid_arr)
				{
					foreach($lopid_arr as $key=>$lopid_item)
					{
						//var_dump($lopid_item);
						if($lopid_item['idlop']==$curent_idlop)
						{
							$lopid_item['kind'] = $kind;
							$lopid_arr[$key] = $lopid_item;
							$flag = 1;
						}
					}
					if($flag != 1)
					{
						$lopid_arr[] = $current_item;
					}
				}else{
					$lopid_arr[] = $current_item;
				}
				$lopids = json_encode($lopid_arr);				
				
				$fields = array(
					'type'		=> 0,
			   	 	'current_lopid' => 0,
					'lopids'=>	$lopids		    
				);
				$this->db->set($fields);
				$this->db->where('hocvien.id', $row->id);
				$this->db->update('hocvien');
				if ($this->db->trans_status() === FALSE)
				{
				    $this->db->trans_rollback();
				    return 0;
				}
				else
				{
				    $this->db->trans_commit();
				    return 1;
				}
				
			}
		}
		return 0;
	}
	
	function fthoihoc($id)
	{
		$kind = 1;
		//1. Update hocvien_lophoc:kind
		$this->db->trans_begin();
		//Get obj before update
		$this->db->select('hocvien_lophoc.*');
		$this->db->from('hocvien_lophoc');
		$this->db->where('hocvien_lophoc.id',$id);
		$query = $this->db->get();
		$hvlophoc = $query->row();
		//var_dump($hvlophoc);
		if($hvlophoc->kind != 0)
		{
			return 0;
		}
		
		$fields = array(
				'kind'		=> $kind,
		);		
		$this->db->set($fields);
		$this->db->where('id', $id);
		$this->db->where('kind', 0);
		if($this->db->update('hocvien_lophoc'))
		{
			//. Nếu thành công cập nhật lại type, current_lopid;lopids of hocvien
			//1. Lấy obj student
			$this->db->select('hocvien.*,hocvien_lophoc.idlop');
			$this->db->from('hocvien');
			$this->db->join('hocvien_lophoc','hocvien.id = hocvien_lophoc.idhv');
			$this->db->where('hocvien_lophoc.id',$id);
			$query = $this->db->get();
			$row = $query->row();
			if($row)
			{
				$lopids = '';
				$lopids = $row->lopids; //String with Json data format,
				$curent_idlop = $row->idlop;
				// Convert json data to array
				$lopid_arr = json_decode($lopids,true);
				
				$current_item['idlop']=$curent_idlop;
				$current_item['kind']=$kind;
				// kiểm tra xem đã tồn tại lopid chưa, có thì cập nhật, chưa thì thêm mới
				$flag = -1;
				if($lopid_arr)
				{
					foreach($lopid_arr as $key=>$lopid_item)
					{
						//var_dump($lopid_item);
						if($lopid_item['idlop']==$curent_idlop)
						{
							$lopid_item['kind'] = $kind;
							$lopid_arr[$key] = $lopid_item;
							$flag = 1;
						}
					}
					if($flag != 1)
					{
						$lopid_arr[] = $current_item;
					}
				}else{
					$lopid_arr[] = $current_item;
				}
				$lopids = json_encode($lopid_arr);				
				
				$fields = array(
					'type'		=> 0,
			   	 	'current_lopid' => 0,
					'lopids'=>	$lopids		    
				);
				$this->db->set($fields);
				$this->db->where('hocvien.id', $row->id);
				$this->db->update('hocvien');
				if ($this->db->trans_status() === FALSE)
				{
				    $this->db->trans_rollback();				    
				    return 0;
				}
				else
				{
				    $this->db->trans_commit();
				    return 1;
				}
				//return 1;
				
			}
		}
		return 0;
	}
	
	function ajax_search_student($keyword)
	{
		$this->db->select('id, mahv, tenhv, cmnd, ngaysinh, nguyenquan');
		$this->db->from('hocvien');
		$this->db->like('mahv',$keyword);
		$this->db->or_like('tenhv',$keyword);
		$this->db->or_like('cmnd',$keyword);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	// =============================
	// = ========== GET ========== =
	// =============================
	
	// Sàng lọc dữ liệu trước khi insert vào csdl
	function phanmon($idlop, $idmons, $idgvs)
	{
		foreach ($idmons as $idmon)
		{
			foreach ($idgvs as $idgv)
			{
				$this->phanmon_to_lop($idlop, $idgv, $idmon);
			}
		}
	}
	
	
	// Lưu dữ liệu phân môn vào bảng phân môn
	function savephanmon($idlop, $array)
	{		
		foreach ($array as $arr)
		{
			$idmon = $arr['mon_id'];
			foreach ($arr['gv_id'] as $gvid)
			{
				if(!$this->hastherecord($idlop, $idmon, $gvid)) // Nếu tồn tại ko thêm nữa.
				{
					$this->phanmon_to_lop($idlop, $idmon , $gvid);
				}
			}
			
		}
		return true;
		
	}
	// Lây dữ liệu ra xem có tồn tại bản ghi chưa có trả true
	function hastherecord($lop_id,$mon_id,$gv_id)
	{
		$this->db->select('*');
		$this->db->from('phanmon');
		$this->db->where('lop_id',$lop_id);
		$this->db->where('mon_id',$mon_id);
		$this->db->where('gv_id',$gv_id);
		$query = $this->db->get();
		if($query->result_array())
		{
			return true;
		}
		return false;
	}
	
	// Thực hiện phân môn học + giáo viên vào các lớp học
	function phanmon_to_lop($idlop, $idmon, $idgv ) {
		$fields = array(
				'lop_id' => $idlop,
				'gv_id' => $idgv,
				'mon_id'=>$idmon,
				'status' => 1
		);
		$this->db->set($fields);
		$this->db->insert('phanmon');
	}
		
	// get all hocvien from db to add to lop
	function get_all_hocvien_unlimit()
	{
		//$this->db->select('*');
		$this->db->select('id, mahv, tenhv, ngaysinh, nguyenquan, cmnd');
		$this->db->from('hocvien');
		$this->db->order_by('sort','asc');
		//$this->db->where('deleted',0); tắt để chạy sap xếp, xong thì bật lại
		$query = $this->db->get();
		return $query->result_array();
	}
	
	// get all hocvien by current year from db to add to lop
	function get_all_hocvien_by_year()
	{
		$year = date('Y');
		$this->db->select('id, mahv, tenhv, ngaysinh, nguyenquan, cmnd');
		$this->db->from('hocvien');
		$this->db->order_by('id','desc');
		//$this->db->where('YEAR(date_created)',$year);
		//$this->db->or_where('YEAR(date_created)',$year-1);
		$this->db->limit(50);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	// Lấy danh sách các sinh viên trong 1 lớp
	public function get_student_of_class_by_id($idlop)
	{
		$this->db->select('hocvien.*, hocvien_lophoc.kind as hvkind,hocvien_lophoc.id as hvlophoc_id');
		$this->db->from('hocvien','hocvien_lophoc','lophoc');
		$this->db->join('hocvien_lophoc','hocvien_lophoc.idhv = hocvien.id');
		$this->db->join('lophoc','lophoc.id = hocvien_lophoc.idlop');
		$this->db->where('hocvien_lophoc.idlop',$idlop);
		$this->db->order_by('hocvien.sort');
		//$this->db->order_by('hocvien.lastname');
		$query = $this->db->get();
		return $query->result_array();
	}
	
	// get all monhoc from db to add to lop
	function get_all_monhoc_unlimit()
	{
		$this->db->select('id, deleted, mamon, tenmon, sotinchi');
		$this->db->from('monhoc');
		$this->db->order_by('mamon','asc');
		$this->db->where('deleted',0);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	// Lấy danh sách các môn học trong 1 lớp
	public function get_monhoc_of_class_by_id($idlop)
	{
		$this->db->select('monhoc.id, monhoc.id as monID, lophoc.id as lopID, monhoc.mamon, monhoc.tenmon, monhoc.sotinchi, lophoc_monhoc.heso, lophoc_monhoc.order, lophoc_monhoc.lock');
		$this->db->from('monhoc','lophoc_monhoc','lophoc');
		$this->db->join('lophoc_monhoc','lophoc_monhoc.id_mon = monhoc.id');
		$this->db->join('lophoc','lophoc.id = lophoc_monhoc.id_lop');
		$this->db->where('lophoc_monhoc.id_lop',$idlop);
		$this->db->order_by('order','asc');
		$query = $this->db->get();
		$recordsets = $query->result_array();
		$rs = array();
		if($recordsets != null)
		{
			foreach ($recordsets as $record )
			{
				$record['hasMark'] = $this->theSubjecthasMark($record['monID'],$record['lopID']);
				$record['teachers'] = $this->get_teachers_by_mon($record['monID'],$record['lopID']);
				$rs[] = $record;
			}
			return $rs;
		} else {
			return 0;
		}
	}
	/*
	 * Lây danh sách giáo viên của mỗi môn học của lớp học
	 * Lấy user_id của giao viên thay thế cho id của giaovien
	 */
	function get_teachers_by_mon($idmon,$idlop)
	{
		$this->db->select('phanmon.*, giaovien.magv, giaovien.tengv');
		$this->db->from('phanmon','giaovien');
		$this->db->join('giaovien','giaovien.user_id = phanmon.gv_id');
		$this->db->where('lop_id',$idlop);
		$this->db->where('mon_id',$idmon);
		$query = $this->db->get();
		return $query->result_array();
		
	}
	/*
	 * Ham trả lại true nếu môn đã nhập điểm
	 * false nếu chưa nhập điểm
	 */
	function theSubjecthasMark($monID,$lopID)
	{
		$query =  "(
		SELECT id
		FROM bangdiem WHERE id_mon ='$monID' AND id_lop = '$lopID' )";
		$result = $this->db->query($query);
		if($result->num_rows() >= 1)
		{
			$data = $result->row();
			return true;
		}
		else
		{
			return false;
		}
	}
	
	function add_monhoc_to_lop($results, $idlop)
	{
		// Lấy danh sách môn hiện tại
		$current_subjects = $this->get_current_subjects_of_class($idlop);
		
		if($current_subjects == null)
		{
			foreach($results as $selected)
			{
				$heso = $this->input->post('heso_'.$selected);
				$stt = $this->input->post('stt_'.$selected);
				$this->add_subject($selected, $idlop,$heso, $stt);
			}
		}
		else
		{
			// So sánh phần tử khác giữa 2 mảng
			// Môn đã bị xóa
			$current = array_diff($current_subjects,$results);
			
			// Môn mới
			$new = array_diff($results,$current_subjects);
			
			// Nếu môn cũ = môn mới
			if($current == $new)
			{
				//trường hợp không thay đổi gì thì ko làm gì :D
				foreach ($results as $n)
				{
					$heso = $this->input->post('heso_'.$n);
					$order = $this->input->post('stt_'.$n);
					//echo $heso; exit();
					$this->update_subject_of_class($n, $idlop, $heso, $order);
				}
			}
			else {
				if($current)
				{
					foreach ($current as $c)
					{
						//delete mon có id tuong ung
						$this->delete_subject($c, $idlop);
						$this->delete_subject_mark_class($c, $idlop);
					}
				}
				if($new)
				{
					foreach ($new as $n)
					{
						//thêm môn có id tuong ung
						$heso = $this->input->post('heso_'.$n);
						$order = $this->input->post('stt_'.$n);
						$this->add_subject($n, $idlop, $heso, $order);
					}
				}
			}
		}
	}
	
	// xoa điểm của Môn học đã bị xóa khỏi Biên chế lớp
	function delete_subject_mark_class($subject_id, $idlop)
	{
		$this->db->where('id_mon',$subject_id);
		$this->db->where('id_lop',$idlop);
		$this->db->delete('bangdiem');
	}
	
	public function delete_subject($subject_id, $class_id)
	{
		$this->db->where('id_mon',$subject_id);
		$this->db->where('id_lop',$class_id);
		$this->db->delete('lophoc_monhoc');
	}
	
	public function add_subject($subject_id, $class_id, $heso, $order)
	{
		$data = array(
				'id_mon' => $subject_id,
				'id_lop' => $class_id,
				'heso'	=>	$heso,
				'order' => $order,
				'date_modified' => date('Y-m-d H:i:s'),
				'order' => $order,
				'edited_by' => $this->user->id,
		);
		$this->db->set($data);
		$this->db->insert('lophoc_monhoc');
	}
	
	public function update_subject_of_class($subject_id, $class_id, $heso, $order)
	{
		$data = array(
				'heso'	=>	$heso,
				'order' => $order,
		);
		$this->db->set($data);
		$this->db->where('id_mon',$subject_id);
		$this->db->where('id_lop',$class_id);
		$this->db->update('lophoc_monhoc');
	}
	
	public function get_current_subjects_of_class($class_id)
	{
		$this->db->select('id_mon');
		$this->db->from('lophoc_monhoc');
		$this->db->where('id_lop',$class_id);
		$query = $this->db->get();
	
		$subject = array();
		foreach ($query->result_array() as $current_subject)
		{
			$subject[] = $current_subject['id_mon'];
		}
		return $subject;
	}
	
	public function get_current_students_of_class($class_id)
	{
		$this->db->select('idhv');
		$this->db->from('hocvien_lophoc');
		$this->db->where('idlop',$class_id);
		$query = $this->db->get();
	
		$students = array();
		foreach ($query->result_array() as $current_students)
		{
			$students[] = $current_students['idhv'];
		}
		return $students;
	}
	
	// xoa id lop tu bang hocvien_lop
	function delete_lopids_from_lophoc_monhoc($idlop)
	{
		$this->db->where('id_lop',$idlop);
		$this->db->delete('lophoc_monhoc');
	}
	
	// them lop vao bang hocvien_lop
	function insert_lopids_to_lop($idmon, $idlop,$heso,$stt)
	{
		$fields = array(
				'id_mon' => $idmon,
				'id_lop'=> $idlop,
				'heso'=>$heso,
				'order' => $stt
		);
		$this->db->set($fields);
		$this->db->insert('lophoc_monhoc');
	}
	
	
	/**
	 * ADDED BY MANHVT support multi times deposte site
	 * Lây tất cả các bản ghi nộp học phí của một sinh viên
	**/
	/*
	 * Lấy danh sách học viên theo lớp
	 */
	function get_hocvien_by_class()
	{
		$classId = $this->input->post('slclassId');		
		$this->db->select('hocvien.*, lophoc.id as lophocid, lophoc.malop');
		$this->db->from('hocvien','hocvien_lop','lophoc');
		$this->db->join('hocvien_lophoc','hocvien_lophoc.idhv = hocvien.id');
		$this->db->join('lophoc', 'lophoc.id = hocvien_lophoc.idlop');
		$this->db->order_by('hocvien.sort');
		//$this->db->order_by('hocvien.lastname');
		$this->db->where('hocvien_lophoc.idlop',$classId);
		$this->db->where('hocvien.deleted',0);
		$this->db->where('hocvien.status',1);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	// Add hoc vien vao lop
	
	// Thêm học viên vào lớp học
	function add_students_to_class($results, $idlop)
	{
		// Lấy danh sách HV hiện tại
		$current_students = $this->get_current_students_of_class($idlop);
		
		if($current_students == null)
		{
			foreach($results as $selected)
			{
				$this->add_student_to_class($selected, $idlop);
			}
		}
		else
		{
			// So sánh phần tử khác giữa 2 mảng
			// Môn đã bị xóa
			$current = array_diff($current_students,$results);
				
			// HV mới
			$new = array_diff($results,$current_students);
				
			// Nếu HV cũ = HV mới
			if($current == $new)
			{
				//trường hợp không thay đổi gì thì ko làm gì :D
			}
			else {
				if($current)
				{
					foreach ($current as $c)
					{
						//delete HV có id tuong ung + xóa bảng điểm HV đó từ bảng điểm
						$this->delete_student_from_class($c, $idlop);
						$this->delete_mark_of_student_from_class($c, $idlop);
					}
				}
				if($new)
				{
					foreach ($new as $n)
					{
						//thêm HV có id tuong ung
						$heso = $this->input->post('heso_'.$n);
						$order = $this->input->post('stt_'.$n);
						$this->add_student_to_class($n, $idlop);
						
						//echo $studentid; exit();
						$subjects = $this->get_subject_have_mark($idlop);
						//var_dump($subjects);
						if($subjects)
						{
							foreach ($subjects as $subject)
							{
								$this->add_mark_of_student_to_lop($n, $subject['id_mon'], $idlop);
							}
						}
					}
				}
			}
		}
	}
	// xoa id HV tu bang hocvien_lophoc
	function delete_student_from_class($idhv, $idlop)
	{
		$this->db->where('idhv',$idhv);
		$this->db->where('idlop',$idlop);
		$this->db->delete('hocvien_lophoc');
	}
	
	// xoa điểm của HV ở lớp đã bị loại
	function delete_mark_of_student_from_class($idhv, $idlop)
	{
		$this->db->where('id_hv',$idhv);
		$this->db->where('id_lop',$idlop);
		$this->db->delete('bangdiem');
	}
	
	function get_subject_have_mark($idlop)
	{
		$this->db->select('id_mon');
		$this->db->group_by('id_mon');
		$this->db->from('bangdiem');
		$this->db->where('id_lop',$idlop);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	public function add_mark_of_student_to_lop($n, $subject, $idlop) // $n = idhv
	{
		$fields = array(
				'id_hv' => $n,
				'id_lop'=> $idlop,
				'id_mon'=> $subject,
				'diemtrungbinh' => 0,
				'date_created' => date('Y-m-d H:i:s'),
				'status' => 0,
		);
		$this->db->set($fields);
		$this->db->insert('bangdiem');
		return $this->db->insert_id();
	}
	
	// them HV vao bang hocvien_lophoc
	function add_student_to_class($idhv, $idlop)
	{
		$this->db->trans_begin();
		$fields = array(
				'idhv' => $idhv,
				'idlop'=> $idlop,
		);
		$this->db->set($fields);
		$this->db->insert('hocvien_lophoc');
		
		//2. cập nhật vào hocvien:type =1; current_idlop = idlop
		/*
		 * Added By ManhVT 03/11/2014
		 */
		$fields = array(
		        'type'=>1,
		        'current_lopid'=>$idlop
		        );
		$this->db->set($fields);
		$this->db->where('id',$idhv);
		$this->db->update('hocvien');
		if ($this->db->trans_status() === FALSE)
		{
		    $this->db->trans_rollback();
		}
		else
		{
		    $this->db->trans_commit();
		}
	}
	
	
	/*
	 * Lấy tất cả các lớp
	 */
	function get_classes()
	{
		$this->db->select('*');
		$this->db->from('lophoc');
		$this->db->order_by('id','desc');
		$this->db->where('deleted',0);
		$this->db->where('status',1);
		$query = $this->db->get();
		return $query->result_array();
	}
	function get_tinh()
	{
		$this->db->select('*');
		$this->db->from('area');
		$this->db->order_by('name','desc');
		$this->db->where('type',0);
		$query = $this->db->get();
		if($query->result())
		{
        	$countries[0] = "Chọn tỉnh";
			foreach ($query->result() as $country)
        	{
            	$countries[$country->id] = $country->name;
          	}
          	
      		return $countries;
      	}
      	else
      	{
      		return FALSE;
      	}
	}
	function get_huyens($tinh_id)
	{
		$this->db->select('*');
		$this->db->from('area');
		$this->db->order_by('name','desc');
		$this->db->where('parent_id',$tinh_id);
		$query = $this->db->get();
		
		$cities = array(); 
      	if($query->result()){
      		$cities[0] = "Chọn ...";
          	foreach ($query->result() as $city) {
              	$cities[$city->id] = $city->name;
          	}
      		return $cities;
      	}
      	else
      	{
        	  return FALSE;
      	}
	}
	function get_xas($huyen_id)
	{
		return $this->get_huyens($huyen_id);
	}
	
	/*
	 * Chức năng học phí
	 * 
	 */
	/*
	 * Đếm toàn bộ bản ghi học phí từ ngày đến ngày
	 */
	
	function count_all_hocphi_custom($from,$to)
	{
		$arr = explode('/',$from);
		$fromDate = $arr[2]."-".$arr[1].'-'.$arr[0];
		$arr = explode('/',$to);
		$toDate = $arr[2]."-".$arr[1].'-'.$arr[0];
		$this->db->select('id');
		$this->db->from('hocphi');
		$this->db->where('deleted',0);
		$this->db->where('hocphi.ngaythu > ',$toDate);
		$this->db->where('hocphi.ngaythu < ',$fromDate);
		return $this->db->count_all_results();
	}
	
	function get_list_hoc_phi_from_to($from,$to)
	{
		$arr = explode('/',$from);
		$fromDate = $arr[2]."-".$arr[1].'-'.$arr[0];
		$arr = explode('/',$to);
		$toDate = $arr[2]."-".$arr[1].'-'.$arr[0];
		//echo $from;exit();
		$this->db->select('hocphi.*,giaovien.id as giaovienid, giaovien.tengv, hocvien.id as hocvienid, hocvien.mahv, hocvien.tenhv, hocvien.cmnd');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.lock',1);
		$this->db->where('hocphi.ngaythu > ',$toDate);
		$this->db->where('hocphi.ngaythu < ',$fromDate);
		//$this->db->where('nguoithu_id', $this->user->id);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function get_total_hoc_phi_from_to($from,$to)
	{
		$arr = explode('/',$from);
		$fromDate = $arr[2]."-".$arr[1].'-'.$arr[0];
		$arr = explode('/',$to);
		$toDate = $arr[2]."-".$arr[1].'-'.$arr[0];
		
		$this->db->select('SUM(hocphi.sotiennop) as total');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.lock',1);
		$this->db->where('hocphi.ngaythu > ',$toDate);
		$this->db->where('hocphi.ngaythu < ',$fromDate);
		//$this->db->where('nguoithu', $this->user->id);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		$return = $query->result_array();
		return $return[0]['total'];
	}
	
	
	function get_list_hoc_phi_duration($duration='')
	{
		$duration = strtoupper($duration);
		switch ($duration)
		{
			case 'THISMONTH':
					return $this->get_list_hoc_phi_thismonth(); break;
			case 'THISYEAR':
					return $this->get_list_hoc_phi_thisyear(); break;
			case 'QUATER':
					return $this->get_list_hoc_phi_thisquater(); break;
			case 'HALF':
					return $this->get_list_hoc_phi_halfyear(); break;
			case 'YESTERDAY':	
					return $this->get_list_hoc_phi_yesterday(); break;
			default :
				    return $this->get_list_hoc_phi_thisday(); break;						
		}
		
	}
	
	function get_total_hoc_phi_duration($duration)
	{
		$duration = strtoupper($duration);
		switch ($duration)
		{
			case 'THISMONTH':
					return $this->get_total_hoc_phi_thismonth(); break;
			case 'THISYEAR':
					return $this->get_total_hoc_phi_thisyear(); break;
			case 'QUATER':
					return $this->get_total_hoc_phi_thisquater(); break;
			case 'HALF':
					return $this->get_total_hoc_phi_halfyear(); break;
			case 'YESTERDAY':	
					return $this->get_total_hoc_phi_yesterday(); break;
			default :
				    return $this->get_total_hoc_phi_thisday(); break;						
		}
		
	}
	/*
	 * Lấy danh sách lịch sử thu học phí trong tháng hiện thời
	 * RETURN: mảng danh sách học viên đóng học phí trong tháng hiện thời
	 */
	function get_list_hoc_phi_thismonth()
	{
		$this->db->select('hocphi.*,giaovien.id as giaovienid, giaovien.tengv, hocvien.id as hocvienid, hocvien.mahv, hocvien.tenhv, hocvien.cmnd');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.lock',1);
		$this->db->where('YEAR(hocphi.ngaythu)',date('Y'));
		$this->db->where('MONTH(hocphi.ngaythu)',date('m'));
		//$this->db->where('nguoithu', $this->user->id);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function get_total_hoc_phi_thismonth()
	{
		$this->db->select('SUM(hocphi.sotiennop) as total');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.lock',1);
		$this->db->where('YEAR(hocphi.ngaythu)',date('Y'));
		$this->db->where('MONTH(hocphi.ngaythu)',date('m'));
		//$this->db->where('nguoithu', $this->user->id);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		$return = $query->result_array();
		return $return[0]['total'];
	}
	/*
	 * Lấy danh sách lịch sử thu học phí trong năm hiện thời
	 * RETURN: mảng danh sách học viên đóng học phí trong năm hiện thời
	 */
	function get_list_hoc_phi_thisyear()
	{
		$this->db->select('hocphi.*, hocvien.id as hocvienid, hocvien.mahv, hocvien.tenhv, hocvien.cmnd as hvcmnd');
		$this->db->from('hocphi','hocvien');
		//$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id','left');
		$this->db->where('hocphi.lock',1);
		$this->db->where('YEAR(hocphi.ngaythu)',date('Y'));
		//$this->db->where('nguoithu', $this->user->id);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function get_total_hoc_phi_thisyear()
	{
		$this->db->select('SUM(hocphi.sotiennop) as total');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.lock',1);
		$this->db->where('YEAR(hocphi.ngaythu)',date('Y'));
		//$this->db->where('nguoithu', $this->user->id);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		$return = $query->result_array();
		return $return[0]['total'];
	}
	/*
	 * Lấy danh sách lịch sử thu học phí trong quý hiện thời
	 * RETURN: mảng danh sách học viên đóng học phí trong quý hiện thời
	 */
	function get_list_hoc_phi_thisquater()
	{
		$result = array();
		return $result;
	}
	/*
	 * Lấy danh sách lịch sử thu học phí trong 6 tháng hiện thời
	 * RETURN: mảng danh sách học viên đóng học phí trong 6 tháng hiện thời
	 */
	function get_list_hoc_phi_halfyear()
	{
		$result = array();
		return $result;
	}
	/*
	 * Lấy danh sách lịch sử thu học phí trong ngày hiện thời
	 * RETURN: mảng danh sách học viên đóng học phí trong ngày hiện thời
	 */
	function get_list_hoc_phi_thisday()
	{
		$this->db->select('hocphi.*,giaovien.id as giaovienid, giaovien.tengv, hocvien.id as hocvienid, hocvien.mahv, hocvien.tenhv, hocvien.cmnd');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.lock',1);
		$this->db->where('date(hocphi.ngaythu)',date('Y-m-d'));
		//$this->db->where('nguoithu', $this->user->id);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function get_total_hoc_phi_thisday()
	{
		$this->db->select('SUM(hocphi.sotiennop) as total');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.lock',1);
		$this->db->where('date(hocphi.ngaythu)',date('Y-m-d'));
		//$this->db->where('nguoithu', $this->user->id);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		$return = $query->result_array();
		return $return[0]['total'];
	}
	/*
	 * Lấy danh sách lịch sử thu học phí trong ngày hôm qua
	 * RETURN: mảng danh sách học viên đóng học phí trong hôm qua
	 */
	function get_list_hoc_phi_yesterday()
	{
		$result = array();
		$this->db->select('hocphi.*,giaovien.id as giaovienid, giaovien.tengv, hocvien.id as hocvienid, hocvien.mahv, hocvien.tenhv, hocvien.cmnd');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.lock',1);
		//$this->db->where('nguoithu', $this->user->id);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result_array();
		//return $result;
	}
	
	function get_count_hoc_phi_yesterday()
	{
		
	}
	
	function ajax_save_hocphi($id, $sotienthu, $sotienphainop)
	{
		$conno = $sotienphainop - $sotienthu;
		$fields = array(
			'sotiennop'			=> $sotienthu,
			'edited_by'			=> $this->user->id,
			'conno'				=> $conno
		);		
		$this->db->set($fields);
		$this->db->where('id', $id);
		$result = $this->db->update('hocphi');
		return $result;
	}
	function get_histotyOf_deposite_by_student($student_id)
	{
		$this->db->select('hocphi.*,giaovien.id as giaovienid, giaovien.tengv, hocvien.id as hocvienid, hocvien.mahv, hocvien.tenhv, hocvien.cmnd');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.deleted',0);
		$this->db->where('nguoinop_id', $student_id);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result_array();
	}
	/*
	 * Lấy tất cả các hạng mục nộp của sinh viên
	 */
	function get_title_of_history_by_student($student_id)
	{
		$this->db->select('hocphi.title,hocphi.id,hocphi.phainop,hocphi.sotiennop');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.deleted',0);
		$this->db->where('nguoinop_id', $student_id);
		$this->db->where('hocphi.parent_id', 0);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function get_info_deposite_by_student($student_id)
	{
		$arrs = $this->get_histotyOf_deposite_by_student($student_id);
		$items = $this->get_title_of_history_by_student($student_id);
		//var_dump($items);
		$return = array();
		foreach($items as $item)
		{
			$return[$item['id']]['title'] = $item['title'];
			$return[$item['id']]['phainop'] = $item['phainop'];
			$danop = $item['sotiennop'];
			foreach($arrs as $arr)
			{
				if($arr['parent_id']== $item['id'])
				{
					$danop = $danop + $arr['sotiennop'];	
				}
			}
			$return[$item['id']]['danop'] = $danop;
			$return[$item['id']]['conno'] = $item['phainop'] - $danop;
		}
		//var_dump($return);
		return $return;
	}
	
	/*
	 * End MANHVT
	 */
	/**
	 * Get all Categories
	 *
	 * @access	public
	 * @return 	object	Categories
	 */
	//Lấy danh sách tất cả các học viên
	public function do_update()
	{
		$datas = $this->get_all_hv();
		foreach ($datas as $data)
		{
			$this->update_lop($data['id'],$data['id_lop']);
		}
		
		echo ' thành công';
	}
	
	function update_lop($idhv, $idlop)
	{
		$fields = array(
				'idhv'		=> $idhv,
				'idlop'		=> $idlop,
		);
		
		$this->db->set($fields);
		$this->db->insert('hocvien_lophoc');
	}
	
	function get_all_hv()
	{
		$this->db->select('id, mahv, tenhv, ngaysinh, cmnd, bodoi, status');
		$this->db->from('hocvien');
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function get_all_hocvien($limit, $start)
	{
		$this->db->select('id, mahv, tenhv, ngaysinh, cmnd, bodoi, status');
		$this->db->from('hocvien');
		$this->db->order_by('id','desc');
		$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	//Lấy thông tin 1 học viên
	function get_hv($id)
	{
		$this->db->select('hocvien.*');
		$this->db->from('hocvien');
		$this->db->where('hocvien.id',$id);
		$query = $this->db->get();
		if($query->num_rows() == 1)
		{
			return $query->row_array();
		}
		else
		{
			return null;
		}
	}
	
	// Lấy thông tin học phí
	function get_thong_tin_hoc_phi($id)
	{
		$this->db->select('hocvien.*, hocphi.id as hocphiid, hocphi.phainop, hocphi.sotiennop, hocphi.conno, hocphi.noidungthu, hocphi.ngaythu, hocphi.nguoithu_id, hocphi.nguoinop_id, hocphi.type, hocphi.hoten as ahoten, hocphi.ngaysinh as angaysinh, hocphi.lophoc as alophoc, hocphi.cmnd as acmnd, hocphi.diachi as adiachi');
		$this->db->from('hocphi','hocvien');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id','left');
		$this->db->where('hocphi.id',$id);
		$query = $this->db->get();
		if($query->num_rows() == 1)
		{
			return $query->row_array();
		}
		else
		{
			return null;
		}
	}
	
	function get_thu_le()
	{
		$this->db->select('*');
		$this->db->from('hocphi');
		$this->db->order_by('id','desc');
		$this->db->where('type',1);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function them_thu_le($ho_ten,$ngaysinh,$cmnd,$lop_hoc,$dia_chi,$so_tien,$noi_dung_thu)
	{
		$data = array(
				'hoten'			=> $ho_ten,
				'ngaysinh'		=> $ngaysinh,
				'cmnd'			=> $cmnd,
				'lophoc'		=> $lop_hoc,
				'diachi'		=> $dia_chi,
				'phainop'		=> $so_tien,
				'sotiennop'		=> $so_tien,
				'nguoinop_id'	=> 0,
				'nguoithu_id'	=> $this->user->id,
				'conno'			=> 0,
				'noidungthu'	=> $noi_dung_thu,
				'type'	=> 1,
		);
		
		$this->db->set($data);
		$this->db->insert('hocphi');
		return $this->db->insert_id();
	}
	
	
	function save_thu_le($id,$ho_ten,$ngaysinh,$cmnd,$lop_hoc,$dia_chi,$so_tien,$noi_dung_thu)
	{
		$data = array(
				'hoten'			=> $ho_ten,
				'ngaysinh'		=> $ngaysinh,
				'cmnd'			=> $cmnd,
				'lophoc'		=> $lop_hoc,
				'diachi'		=> $dia_chi,
				'phainop'		=> $so_tien,
				'sotiennop'		=> $so_tien,
				'conno'			=> 0,
				'noidungthu'	=> $noi_dung_thu,
		);
	
		$this->db->set($data);
		$this->db->where('id',$id);
		return $this->db->update('hocphi');
	}
	
	function get_all_lop_by_year($year,$limit, $start)
	{
		//echo $year;
		$this->db->select('*');
		$this->db->from('lophoc');
		$this->db->order_by('id','desc');
		$this->db->limit($limit, $start);
		$this->db->where('deleted',0);
		$this->db->where('status',1);
		$this->db->where('YEAR(lophoc.nambatdau)',$year);
		$query = $this->db->get();
		return $query->result_array();
	}
	//Lấy tất cả các lớp chưa bị xóa và đang active
	function get_all_lop($limit, $start)
	{
		$this->db->select('*');
		$this->db->from('lophoc');
		$this->db->order_by('id','desc');
		$this->db->limit($limit, $start);
		$this->db->where('deleted',0);
		//$this->db->where('status',1); tạm thởi bỏ
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function get_all_khoa()
	{
		$this->db->select('*');
		$this->db->from('khoa');
		$this->db->order_by('id','desc');
		$this->db->where('deleted',0);
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
			return $query->result_array();
		}else{
			return null;
		}
	}
	
	function get_all_giaovien()
	{
		$this->db->select('id, magv, tengv, user_id');
		$this->db->from('giaovien');
		
		$this->db->where('deleted',0);
		$this->db->order_by('tengv','asc');
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
			return $query->result_array();
		}else{
			return null;
		}
	}
	
	function get_all_thanhvien($limit, $start)
	{
		$this->db->select('user.*, user_group.group_id, user_group.title');
		$this->db->from('user',' user_group');
		$this->db->join('user_group','user_group.group_id = user.id');
		$this->db->order_by('user.id','desc');
		$this->db->where('user.deleted',0);
		$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	
	function get_tenlop()
	{
		$this->db->select('*');
		$this->db->from('lophoc');
		$this->db->where('status',1);
		$this->db->where('deleted',0);
		$this->db->order_by('tenlop','ASC');		
		$query = $this->db->get();
		return $query->result();
	}

		
	function get_all_hocphi($limit, $start)
	{
		$this->db->select('hocphi.*, hocvien.id as hocvienid, hocvien.mahv, hocvien.tenhv, hocvien.cmnd as hvcmnd, hocvien.ngaysinh as hvngaysinh');
		$this->db->from('hocphi','hocvien');
		//$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id','left');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id','left');
		$this->db->where('hocphi.deleted',0);
		//$this->db->where('nguoithu', $this->user->id);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function get_items()
	{
		$this->db->select('hocphi.title,hocphi.id,hocphi.phainop,hocphi.sotiennop, giaovien.id as giaovienid, giaovien.tengv, hocvien.id as hocvienid, hocvien.mahv, hocvien.tenhv, hocvien.cmnd, hocphi.parent_id, hocphi.conno, hocphi.ngaythu');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.parent_id', 0);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result_array();
	}
	function get_congno_by_item($item_id)
	{
		$this->db->select('hocphi.id as maxid,giaovien.id as giaovienid, giaovien.tengv, hocvien.id as hocvienid, hocvien.mahv, hocvien.tenhv, hocvien.cmnd, hocphi.parent_id, hocphi.conno,hocphi.title, hocphi.ngaythu ');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.parent_id = ',$item_id);
		$this->db->order_by('hocphi.id','desc');
		$this->db->limit(1);
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
			return $query->result_array();
		}else{
			return null;
		}
	}
	function get_congno()
	{
		$items = $this->get_items();
		$return = array();
		foreach($items as $item)
		{
			$aitem = $this->get_congno_by_item($item['id']);
			if($aitem[0]['maxid']== null)
			{
				
				if($item["conno"] > 0)
				{
					$return[] = $item;
				}
			}
			else
			{
				if($aitem[0]['conno'] > 0)
				{
					$aitem[0]['title']= $item['title'];
					$return[] = $aitem[0];
				}
			}
		}
		//var_dump($return[0]);exit();
		/*
		$this->db->select('hocphi.*,giaovien.id as giaovienid, giaovien.tengv, hocvien.id as hocvienid, hocvien.mahv, hocvien.tenhv, hocvien.cmnd');
		$this->db->from('hocphi','giaovien','hocvien');
		$this->db->join('giaovien', 'giaovien.id = hocphi.nguoithu_id');
		$this->db->join('hocvien', 'hocvien.id = hocphi.nguoinop_id');
		$this->db->where('hocphi.deleted',0);
		$this->db->where('hocphi.conno >', 0);
		$this->db->order_by('hocphi.id','desc');
		//$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result_array();
		*/
		return $return;
	}
	
	// --------------------------------------------------------------------
	
	// Thêm mới Học viên
	function add_hocvien()
	{
		$this->load->helper('string');
		//$ngaysinh = ($this->input->post('ngaysinh') != '') ? date('Y-m-d', strtotime($this->input->post('ngaysinh'))) : date('Y-m-d');
		$date = str_replace('/', '-', $this->input->post('ngaysinh'));
		$ngaysinh = date('Y-m-d', strtotime($date));
		
		$date2 = str_replace('/', '-', $this->input->post('ngaycap'));
		$ngaycap = date('Y-m-d', strtotime($date2));		
		
		if($this->input->post('bodoi')==0)
		{
			$sothexuatngu = '';
			$date3 = str_replace('/', '-', $this->input->post('ngayxuatngu'));
			$ngayxuatngu = date('Y-m-d', strtotime($date3));
		}
		else
		{
			$sothexuatngu = $this->input->post('sothexuatngu');
			$date3 = str_replace('/', '-', $this->input->post('ngayxuatngu'));
			$ngayxuatngu = date('Y-m-d', strtotime($date3));
		}
		if($this->input->post('tenbo') != '')
		{
			$tenbo = $this->input->post('tenbo');
		}
		else {
			$tenbo = '';
		}
		if($this->input->post('tenme') != '')
		{
			$tenme = $this->input->post('tenme');
		}
		else {
			$tenme = '';
		}
		if($this->input->post('tenvochong') != '')
		{
			$tenvochong = $this->input->post('tenvochong');
		}
		else {
			$tenvochong = '';
		}
		if($this->input->post('photo') != '')
		{
			$photo = $this->input->post('photo');
		}
		else {
			$photo = '';
		}
		$tenhv = $this->input->post('tenhv');
		$firstname = '';
		$lastname = '';
		
		$arrten = explode(' ',$tenhv);
		$firstname = $arrten[count($arrten)-1];
		$lastname = $arrten[0];
		for($j = 1; $j < count($arrten)-1;$j++)
		{
			$lastname = $lastname . ' ' .$arrten[$j];
		}
		//$lastname = 
		$sort = endcodevntoasc($firstname.' '.$lastname);
		$data = array(
			//'mahv' 			=> $this->input->post('mahv'),
			'tenhv'			=> mb_strtoupper($this->input->post('tenhv')),
			'email'			=> $this->input->post('email'),
			'didong'		=> $this->input->post('didong'),
			'nguyenquan'	=> $this->input->post('nguyenquan'),
			'thuongtru'		=> $this->input->post('thuongtru'),
			'ngaysinh'		=> $ngaysinh,
			'gioitinh'		=> $this->input->post('gioitinh'),
			'hedaotao'		=> $this->input->post('hedaotao'),
			'id_lop'		=> $this->input->post('malop'),
			'cmnd'			=> $this->input->post('cmnd'),
			'ngaycap'		=> $ngaycap,
			'noicap'		=> $this->input->post('noicap'),
			'bodoi'			=> $this->input->post('bodoi'),
			'sothexuatngu'	=> $sothexuatngu,
			'ngayxuatngu'	=> $ngayxuatngu,
			'soquyetdinh'	=> $this->input->post('soquyetdinh'),
			'hocvan'		=> $this->input->post('hocvan'),
			'created_by'	=> $this->user->id,
			'date_created'	=> $datetime = date('Y-m-d H:i:s'),
			'status'		=> 1,
				'photo'     => $photo,
				'tenbo'=>$tenbo,
				'tenme'=>$tenme,
				'tenvochong'=>$tenvochong,
				'firstname'=>$firstname,
				'lastname'=>$lastname,
				'sort'=>$sort
		);
		
		$this->db->set($data);
		$this->db->insert('hocvien');
		
		$mat19 = 'T19-'.date('y').'-';
		$idhv = $this->db->insert_id();
		$mahv = $this->sinhmahv($idhv);
		
		$data2 = array(
			'mahv' => $mat19.$mahv,
		);
		
		$this->db->set($data2);
		$this->db->where('id', $idhv);
		$this->db->update('hocvien');
		
	}
	
	// Hàm này tự động tạo MAHV
	public function sinhmahv($idhv)
	{
		$i = 5;
		$len = strlen($idhv);
		$pre ='';
		for($j=0;$j<$i-$len;$j++)
		{
		$pre .= '0';
		}
		$mahv = $pre . "$idhv";
		return $mahv;
	}
	
	// edit object
	public function edit_obj($obj)
	{
			
		
	
		$this->db->set($obj);	
		$this->db->where('id', $obj['id']);
		$this->db->update('hocvien');
	
	}
	// Sửa thông tin Học viên
	public function edit_hocvien($id)
	{
		$this->load->helper('string');
		if($this->input->post('bodoi')==0)
		{
			$sothexuatngu = '';
			$date3 = str_replace('/', '-', $this->input->post('ngayxuatngu'));
			$ngayxuatngu = date('Y-m-d', strtotime($date3));
		}
		else
		{
			$sothexuatngu = $this->input->post('sothexuatngu');
			$date3 = str_replace('/', '-', $this->input->post('ngayxuatngu'));
			$ngayxuatngu = date('Y-m-d', strtotime($date3));
		}
		$date = str_replace('/', '-', $this->input->post('ngaysinh'));
		$ngaysinh = date('Y-m-d', strtotime($date));
		
		$date2 = str_replace('/', '-', $this->input->post('ngaycap'));
		$ngaycap = date('Y-m-d', strtotime($date2));
		
		$date_modified = date('Y:m:d; H:m:s', time());

		$tenhv = $this->input->post('tenhv');
		$firstname = '';
		$lastname = '';
		
		$arrten = explode(' ',$tenhv);
		$firstname = $arrten[count($arrten)-1];
		$lastname = $arrten[0];
		for($j = 1; $j < count($arrten)-1;$j++)
		{
		$lastname = $lastname . ' ' .$arrten[$j];
		}
		$sort = endcodevntoasc($firstname.' '.$lastname);
		$fields = array(
			'tenhv'			=> mb_strtoupper($this->input->post('tenhv')),
			'email'			=> $this->input->post('email'),
			'didong'		=> $this->input->post('didong'),
			'nguyenquan'	=> $this->input->post('nguyenquan'),
			'thuongtru'		=> $this->input->post('thuongtru'),
			'ngaysinh'		=> $ngaysinh,
			'gioitinh'		=> $this->input->post('gioitinh'),
			'hedaotao'		=> $this->input->post('hedaotao'),
			'cmnd'			=> $this->input->post('cmnd'),
			'ngaycap'		=> $ngaycap,
			'noicap'		=> $this->input->post('noicap'),
			'bodoi'			=> $this->input->post('bodoi'),
			'sothexuatngu'	=> $sothexuatngu,
			'ngayxuatngu'	=> $ngayxuatngu,
			'soquyetdinh'	=> $this->input->post('soquyetdinh'),
			'edited_by'		=> $this->user->id,
			'hocvan'		=> $this->input->post('hocvan'),
			'status'		=> 1,
				'tenme'=>$this->input->post('tenme'),
				'tenbo'=>$this->input->post('tenbo'),
				'tenvochong'=>$this->input->post('tenvochong'),
				'photo'=>$this->input->post('photo'),
				'firstname'=>$firstname,
				'lastname'=>$lastname,
				'sort'=>$sort
		);	
		
		$this->db->set($fields);
		
		$this->db->where('id', $id);
		$this->db->update('hocvien');

	}
	
	function get_malop($idlop)
	{
		$this->db->select('id, malop');
		$this->db->from('lophoc');
		$this->db->where('id', $idlop);
		$query = $this->db->get();
		return $query->row_array();
	}
	
	// Đếm tổng số học viên trong 1 lớp
	public function count_all_hocvien_lop($id)
	{
		$this->db->select('id');
		$this->db->from('hocvien');
		$this->db->where('id_lop',$id);
		return $this->db->count_all_results();
	}
	
	//Thu tiền học phí
	function add_hocphi()
	{
		//$ngaysinh = ($this->input->post('ngaysinh') != '') ? date('Y-m-d', strtotime($this->input->post('ngaysinh'))) : date('Y-m-d');
		$data = array(
			'phainop' 	=> $this->input->post('phainop'),
			'noidungthu'	=> $this->input->post('noidungthu'),
			'sotiennop'			=> $this->input->post('lan1'),
			'conno'			=> $this->input->post('phainop') - $this->input->post('lan1'),
			'nguoinop_id'		=> $this->input->post('nguoinop'),
			'nguoithu_id'		=> $this->user->id,
			'status'		=> ($this->input->post('status')==1)?1:0,
			'title'=> $this->input->post('title'),
			'parent_id'=> ($this->input->post('status')==1)?$this->input->post('title'):0
		);
		//echo $this->input->post('quanngu'); exit();
		$this->db->set($data);
		$this->db->insert('hocphi');
		//$data['lastid'] = $this->db->insert_id();
    	//return $data;
	}
	
	function add_lan2($id)
	{
		//$ngaysinh = ($this->input->post('ngaysinh') != '') ? date('Y-m-d', strtotime($this->input->post('ngaysinh'))) : date('Y-m-d');
		$data = array(
			'lan2' 	=> $this->input->post('lan2'),
			'conno'			=> 0,
		);
		//echo $this->input->post('quanngu'); exit();
		$this->db->set($data);
		$this->db->where('id', $id);
		$this->db->update('hocphi');
	}
	
	function getLastInserted() {
	    return $this->db->insert_id();
	}
	
	function add_lop()
	{
		$date = str_replace('/', '-', $this->input->post('nambatdau'));
		$nambatdau = date('Y-m-d', strtotime($date));
		
		$date2 = str_replace('/', '-', $this->input->post('namketthuc'));
		$namketthuc = date('Y-m-d', strtotime($date2));
		$data = array(
			'malop' 			=> $this->input->post('malop'),
			'tenlop'			=> $this->input->post('tenlop'),
			'id_gv'				=> $this->input->post('giaovien'),
			'nambatdau'			=> $nambatdau,
			'namketthuc'		=> $namketthuc,
			'id_khoa'			=> $this->input->post('khoa'),
			'status'			=> $this->input->post('status'),
			'note'				=> $this->input->post('note'),
			'type'			    => $this->input->post('type')
		);
		
		$this->db->set($data);
		$this->db->insert('lophoc');
	}
	
	function add_hocky()
	{
		$data = array(
			'tenhocky' 			=> $this->input->post('tenhocky'),
			'namhoc'			=> $this->input->post('namhoc'),
			'sotienthu'			=> $this->input->post('sotienthu'),
			'noidungthu'		=> $this->input->post('noidungthu'),
			'tungay'			=> $this->input->post('tungay'),
			'denngay'			=> $this->input->post('denngay'),
			'status'			=> $this->input->post('status'),
		);
		
		$this->db->set($data);
		$this->db->insert('hocky');
	}
	
	function add_password($password)//, $email, $change = FALSE)
	{
		$hash = sha1(microtime());
		$salt = $this->config->item('auth');
		$salt = $salt['salt'];

		$password = sha1($salt.$hash.$password);

		//$change = $change ? '1' : '0';
		$data = array(
				//'change_password'		=> $change,
				'password'				=> $password,
				'hash' 					=> $hash
        );

		$this->db->where('email', $email);
		$this->db->update($this->table, $data);
		
		return ($this->db->affected_rows() > 0) ? TRUE : FALSE;
	}
	
	function add_user()
	{
		$password = $this->input->post('password');
		$hash = sha1(microtime());
		$salt = $this->config->item('auth');
		$salt = $salt['salt'];

		$password = sha1($salt.$hash.$password);
		$data = array(
			'username' 			=> $this->input->post('username'),
			'password'			=> $password,
			'hash'				=> $hash,
			'email'				=> $this->input->post('email'),
			'fullname'			=> $this->input->post('fullname'),
			'mobile'			=> $this->input->post('didong'),
			'FK_group_id'		=> $this->input->post('group'),
			'active'			=> $this->input->post('status'),
		);
		
		$this->db->set($data);
		$this->db->insert('user');
	}
	
	public function check_mahv()
	{
		$mahv = $this->input->post('mahv');
        $query =  "
			SELECT id, mahv
			FROM hocvien WHERE mahv ='$mahv' 
			";
        $result = $this->db->query($query);
        if($result->num_rows() >= 1)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    
	public function check_cmnd()
	{
        $this->db->where('cmnd',$this->input->post('cmnd'));
        $query = $this->db->get('hocvien');
        if($query->num_rows() == 1)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    
	public function check_malop()
	{
        $this->db->where('malop',$this->input->post('malop'));
        $query = $this->db->get('lophoc');
        if($query->num_rows() == 1)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    
	public function check_username()
	{
        $this->db->where('username',$this->input->post('username'));
        $query = $this->db->get('user');
        if($query->num_rows() == 1)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    
	public function check_email()
	{
        $this->db->where('email',$this->input->post('email'));
        $query = $this->db->get('user');
        if($query->num_rows() == 1)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    
	public function check_mail()
	{
        $this->db->where('email',$this->input->post('email'));
        $query = $this->db->get('hocvien');
        if($query->num_rows() == 1)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    
	public function get_hocvien_info($id)
	{
		$datanew = array();
		$this->db->select('*');
		$this->db->from('hocvien');
		$this->db->where('id', $id);
		$query = $this->db->get();
		return $query->row_array();
		
	}
	
	public function get_hocvien_info_bk($id)
	{
		$datanew = array();
		$this->db->select('*');
		$this->db->from('hocvien');
		$this->db->where('id', $id);
		$query = $this->db->get();
		if($query->num_rows > 0)
		{
			$datanew = $query->row_array();
				
			$this->db->select('lophoc.malop,lophoc.tenlop, hocvien_lophoc.idlop, hocvien_lophoc.idhv');
			$this->db->from('lophoc','hocvien_lophoc');
			$this->db->join('hocvien_lophoc','hocvien_lophoc.idlop = lophoc.id');
			$this->db->where('hocvien_lophoc.idhv',$id);
			$query = $this->db->get();
			if($query->num_rows() > 0 )
			{
				$datanew['lopIds'] = $query->result_array();
			}
			else
			{
				return $datanew['lopIds'] = NULL;
			}
		}
	
		else
		{
			$datanew = NULL;
		}
		return $datanew;
	
	}
	
	public function get_hocphi_info($id)
	{
		$this->db->select('*');
		$this->db->from('hocphi');
		$this->db->where('id', $id);
		$query = $this->db->get();
		return $query->row();
	}
	
	public function get_hocky_info($id)
	{
		$this->db->select('*');
		$this->db->from('hocky');
		$this->db->where('id', $id);
		$query = $this->db->get();
		return $query->row();
	}
	
	public function get_user_info($id)
	{
		$this->db->select('*');
		$this->db->from('user');
		$this->db->where('id', $id);
		$query = $this->db->get();
		return $query->row();
	}
	
	public function get_lop_info($id)
	{
		$this->db->select('*');
		$this->db->from('lophoc');
		$this->db->where('id', $id);
		$query = $this->db->get();
		return $query->row();
	}
	
	
	public function edit_lop($id)
	{
		
		$date = str_replace('/', '-', $this->input->post('nambatdau'));
		$nambatdau = date('Y-m-d', strtotime($date));
		
		$date2 = str_replace('/', '-', $this->input->post('namketthuc'));
		$namketthuc = date('Y-m-d', strtotime($date2));
		$fields = array(
			'malop' 			=> $this->input->post('malop'),
			'tenlop'			=> $this->input->post('tenlop'),
			'id_gv'				=> $this->input->post('giaovien'),
			'nambatdau'			=> $nambatdau,
			'namketthuc'		=> $namketthuc,
			'id_khoa'			=> $this->input->post('khoa'),
			'note'			=> $this->input->post('note'),
			'status'			=> $this->input->post('status'),
			'type'			=> $this->input->post('type'),
		);
		
		$this->db->set($fields);
		$this->db->where('id', $id);
		$this->db->update('lophoc');
	}
	
	public function edit_hocky($id)
	{
		$fields = array(
			'tenhocky' 			=> $this->input->post('tenhocky'),
			'namhoc'			=> $this->input->post('namhoc'),
			'sotienthu'			=> $this->input->post('sotienthu'),
			'noidungthu'		=> $this->input->post('noidungthu'),
			'tungay'			=> $this->input->post('tungay'),
			'denngay'			=> $this->input->post('denngay'),
			'status'			=> $this->input->post('status'),
		);
		
		
		$this->db->set($fields);
		
		$this->db->where('id', $id);
		$this->db->update('hocky');
	}
	
	public function edit_hocphi($id, $sotienthu)
	{
		//$ngaysinh = ($this->input->post('ngaysinh') != '') ? date('Y-m-d', strtotime($this->input->post('ngaysinh'))) : date('Y-m-d');
		
		//$time = mdate('%dd/%mm/%yyyy %H:%m:%s',time());
		$fields = array(
			'sotienthu'			=> $sotienthu,
			'edited_by'			=> $this->user->id,
		);
		
		$this->db->set($fields);
		$this->db->where('id', $id);
		$this->db->update('hocphi');
	}
	
	function edit_user($id)
	{
		$password = $this->input->post('password');
		$hash = sha1(microtime());
		$salt = $this->config->item('auth');
		$salt = $salt['salt'];

		$password = sha1($salt.$hash.$password);
		$data = array(
			'username' 			=> $this->input->post('username'),
			'email'				=> $this->input->post('email'),
			'fullname'			=> $this->input->post('fullname'),
			'mobile'			=> $this->input->post('didong'),
			'FK_group_id'		=> $this->input->post('group'),
			'active'			=> $this->input->post('status'),
		);
		
		$data2 = array(
			'username' 			=> $this->input->post('username'),
			'password'			=> $password,
			'hash'				=> $hash,
			'email'				=> $this->input->post('email'),
			'fullname'			=> $this->input->post('fullname'),
			'mobile'			=> $this->input->post('didong'),
			'FK_group_id'		=> $this->input->post('group'),
			'active'			=> $this->input->post('status'),
		);
		if($this->input->post('password') == '')
		{
			$this->db->set($data);
		}
		else
		{
			$this->db->set($data2);
		}
		
		$this->db->where('id', $id);
		$this->db->update('user');
	}
	
	function lock_hocphi($id)
	{
		$data = array('lock' => 1);
		$this->db->where('id', $id);
		$this->db->set($data);
		$this->db->update('hocphi');
		if($this->db->affected_rows() >= 0)
		{
			return TRUE;
		}
		else
		{
			return FALSE;
		}
	}
	
	function delete_user($id)
	{
		$this->db->where('id', $id);
		$this->db->delete('user');
	}
	
	function delete_hocky($id)
	{
		$this->db->where('id', $id);
		$this->db->delete('hocky');
	}
	
	function delete_lop($id)
	{
		$this->db->where('id', $id);
		$this->db->delete('lop');
	}

	public function change_status_hocvien($id = null,$status = 0)
	{
		$data = array
				(
					'status'=> $status
				);
		$this->db->where('id',$id);
		$this->db->update('hocvien',$data);		
	}
	
	public function change_status_lop($id = null,$status = 0)
	{
		$data = array
				(
					'status'=> $status
				);
		$this->db->where('id',$id);
		$this->db->update('lophoc',$data);		
	}
	
	public function lockhv($id = null,$status = 0)
	{
		$data = array
		(
				'lock_hv'=> $status
		);
		$this->db->where('id',$id);
		$this->db->update('lophoc',$data);
	}
	
	public function lockmh($id = null,$status = 0)
	{
		$data = array
		(
				'lock_mh'=> $status
		);
		$this->db->where('id',$id);
		$this->db->update('lophoc',$data);
	}
	
	public function change_status_user($id = null,$status = 0)
	{
		$data = array
				(
					'active'=> $status
				);
		$this->db->where('id',$id);
		$this->db->update('user',$data);		
	}


	function count_all_hocphi($duration='')
	{
		$this->db->select('id');
		$this->db->from('hocphi');
		$this->db->where('deleted',0);
		return $this->db->count_all_results();
	}
	
	function count_all_lop_by_year($year)
	{
		//echo $year;
		$this->db->select('id');
		$this->db->from('lophoc');
		$this->db->where('deleted',0);
		$this->db->where('YEAR(lophoc.nambatdau)',$year);
		return $this->db->count_all_results();
	}
	function count_all_lop()
	{
		$this->db->select('id');
		$this->db->from('lophoc');
		$this->db->where('deleted',0);
		return $this->db->count_all_results();
	}
	
	function count_all_bodoi()
	{
		$this->db->select('id');
		$this->db->from('hocvien');
		$this->db->where('bodoi',1);
		$this->db->where('deleted',0);
		return $this->db->count_all_results();
	}

	public function search_data($keyword)
	{
		$this->db->select('id, mahv, tenhv, ngaysinh, cmnd, bodoi, status');
		$this->db->from('hocvien');
		$this->db->like('mahv', $keyword);
		$this->db->or_like('tenhv', $keyword);
		$this->db->or_like('cmnd', $keyword);
		$this->db->order_by('sort', 'asc');
		$this->db->where('status',1);
		$query = $this->db->get();
			
		if ($query->num_rows() > 0)
		{
			$result = $query->result_array();
			return $result;
		}
		else
		{
			return null;
		}
	}
	
	public function search_data_lop($keyword, $limit, $start)
	{
		$this->db->select('*');
		$this->db->from('lophoc');
		
		$this->db->like('lophoc.malop', $keyword);
		$this->db->or_like('lophoc.tenlop', $keyword);
		$this->db->order_by('lophoc.id', 'DESC');
		$this->db->limit($limit, $start);
		$this->db->where('lophoc.deleted',0);
		$query = $this->db->get();
			
		if ($query->num_rows() > 0)
		{
			$result = $query->result_array();
			return $result;
		}
		else
		{
			return null;
		}
	}
	
	public function count_all_user()
	{
		$this->db->select('id');
		$this->db->from('user');
		$this->db->where('deleted',0);
		return $this->db->count_all_results();
	}
	
	public function count_all_hocvien()
	{
		$this->db->select('id');
		$this->db->from('hocvien');
		$this->db->where('deleted',0);
		return $this->db->count_all_results();
	}
	
	public function datahocphisend()
	{
	//echo 'a'; exit();
		$return = array();
		$this->db->select('hocphi.id, hocphi.phainop, hocphi.sotiennop, hocphi.conno, hocphi.noidungthu, 
		hocphi.ngaythu, hocphi.nguoinop_id, hocphi.nguoithu_id, hocphi.status, hocphi.lock, hocphi.deleted,
		hocvien.id as hocvienid, hocvien.mahv, hocvien.tenhv');
		//$this->db->select_sum('hocphi.danop','tongthu');
		$this->db->from('hocphi', 'hocvien');
		$this->db->join('hocvien','hocvien.id = hocphi.nguoinop_id');
		$this->db->where('date(hocphi.ngaythu)',date('Y-m-d'));
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
			//$result = $query->result_array();
			$return['rows'] = $query->result_array();
			$this->db->select_sum('hocphi.sotiennop','tongthu');
			$this->db->from('hocphi', 'hocvien');
			$this->db->join('hocvien','hocvien.id = hocphi.nguoinop_id');
			$this->db->where('date(hocphi.ngaythu)',date('Y-m-d'));
			$query = $this->db->get();
			if ($query->num_rows() > 0)
			{
				$return['tongthu'] = $query->row();
			}
			else
			{
				return $return['tongthu'] = null;
			}
		}
		else
		{
			$return = null;
		}
		//var_dump($return);
		return $return;
	}
	
	public function lop_status_exchange($id, $status = 0)
	{
		$data = array(
				'status' => $status,
		);
	
		$this->db->where('id', $id);
		$this->db->update('lophoc', $data);
		if($this->db->affected_rows() >= 0)
		{
			return TRUE;
		}
		else
		{
			return FALSE;
		}
	}
}

/* Location: ./application/models/qlsv_model.php */